package com.fastloan.jn.mgt.all.table.service.impl;

import com.fastloan.jn.common.BasicServiceImpl;
import com.fastloan.jn.mgt.all.table.dao.*;
import com.fastloan.jn.mgt.all.table.exceptions.TableReaderException;
import com.fastloan.jn.mgt.all.table.model.*;
import com.fastloan.jn.mgt.all.table.service.AllTableService;
import com.fastloan.jn.mgt.application.basic.dao.JnApplicationMapper;
import com.fastloan.jn.mgt.application.basic.model.JnApplication;
import com.fastloan.jn.mgt.application.basic.service.JnApplicationService;
import com.fastloan.safe.core.util.ManagerKit;
import com.kawins.result.ResultEnum;
import com.kawins.result.ResultTo;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.validation.constraints.NotNull;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by gelon on 2017/10/27.
 */
@Service
public class AllTableServiceImpl extends BasicServiceImpl<AllTable> implements AllTableService {

    @Autowired
    AllTableMapper allTableMapper;

    @Autowired
    AllRowMapper rowMapper;

    @Autowired
    AllCellMapper cellMapper;

    @Autowired
    AllCellValueMapper cellValueMapper;

    @Autowired
    AllExcelMapper excelMapper;

    @Autowired
    JnApplicationMapper applicationMapper;


    @Override
    public List<AllTable> getTable(String relevanceId) {
        AllExcel allExcel = excelMapper.selectOne(new AllExcel().setRelevanceId(relevanceId));
        if(allExcel == null) {return null ;}
        List<AllTable> allTables = allTableMapper.select(new AllTable().setExcelId(allExcel.getId()));
        allTables.forEach(allTable -> findRow(allTable, relevanceId));
        return allTables;
    }

    private void findRow(AllTable allTable, String relevanceId) {
        List<AllCellValue> cellValues = cellValueMapper.select(new AllCellValue().setTableId(allTable.getId()));
        List<AllRow> allRows = rowMapper.select(new AllRow().setTableId(allTable.getId()));
        allTable.setAllRows(allRows);
        allRows.forEach(allRow -> findCell(allRow, relevanceId, cellValues));
    }

    private void findCell(AllRow allRow, String relevanceId, List<AllCellValue> cellValues) {
        List<AllCell> allCells = cellMapper.select(new AllCell().setRowId(allRow.getId()));
        allRow.setAllCells(allCells);
        allCells.forEach(allCell -> findCellValue(allCell, cellValues));
    }

    private void findCellValue(AllCell allCell, List<AllCellValue> cellValues) {
        AllCellValue allCellValue = cellValues.stream().filter(cellValue -> cellValue.getCellId().equals(allCell.getId())).findFirst().orElse(null);
        if (allCellValue != null) allCell.setContent(allCellValue.getContent());
    }

    @Override
    @Transactional
    public List<AllTable> saveUnique(InputStream is, String filename, String relevanceId) throws IOException, NoSuchMethodException, IllegalAccessException, InvocationTargetException {
        deleteExist(relevanceId);
        List<AllTable> allTables = readExcel(is);
        insert(allTables, filename, relevanceId);
        return allTables;
    }

    private void deleteExist(String relevanceId) {
        List<AllExcel> allExcel = excelMapper.select(new AllExcel().setRelevanceId(relevanceId));
        allExcel.forEach(this::deleteIfExist);
    }

    private void deleteIfExist(AllExcel allExcel) {
        if(allExcel != null){
            deleteTables(allExcel);
            excelMapper.delete(allExcel);
        }
    }

    private void deleteTables(AllExcel allExcel) {
        List<AllTable> allTables = allTableMapper.select(new AllTable().setExcelId(allExcel.getId()));
        allTables.forEach(this::deleteRows);
        allTableMapper.delete(new AllTable().setExcelId(allExcel.getId()));
    }

    private void deleteRows(AllTable allTable) {
        List<AllRow> allRows = rowMapper.select(new AllRow().setTableId(allTable.getId()));
        allRows.forEach(this::deleteCell);
        rowMapper.delete(new AllRow().setTableId(allTable.getId()));
    }

    private void deleteCell(AllRow allRow) {
        List<AllCell> allCells = cellMapper.select(new AllCell().setRowId(allRow.getId()));
        allCells.forEach(this::deleteCllValue);
        cellMapper.delete(new AllCell().setRowId(allRow.getId()));
    }

    private void deleteCllValue(AllCell allCell) {
        cellValueMapper.delete(new AllCellValue().setCellId(allCell.getId()));
    }


    private List<AllTable> readExcel(InputStream is) throws IOException, NoSuchMethodException, IllegalAccessException, InvocationTargetException {
        XSSFWorkbook wb = new XSSFWorkbook(is);
        List<AllTable> allTables = new ArrayList<>();
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            List<AllCell> allCells = new ArrayList<>();
            XSSFSheet sheet = wb.getSheetAt(i);
            List<CellRangeAddress> range = sheet.getMergedRegions();
            int maxRow = sheet.getLastRowNum();
            int maxC = sheet.getRow(0).getLastCellNum();
            for (int r = 0; r < maxRow; r++) {
                XSSFRow row = sheet.getRow(r);
                for (int c = 0; c < maxC; c++) {
                    XSSFCell cell = row.getCell(c);
                    if (cell == null) continue;
                    AllCell allCell = parse(cell, range, r, c);
                    if (allCell != null) allCells.add(allCell);
                }
            }
            allTables.add(AllTable.build(allCells, maxRow, maxC, sheet.getSheetName()));
        }
        return allTables;
    }

    @Override
    @Transactional
    public void save(List<AllCellValue> allCellValues, String relaId) {
        allCellValues.forEach(cellValue -> saveReplace(cellValue, relaId));
    }

    @Override
    @Transactional
    public void saveAppTables(InputStream is, @NotNull String relevanceId,String filename, String appId)
            throws InvocationTargetException, NoSuchMethodException, IllegalAccessException, IOException,TableReaderException {
        List<AllTable> allTables = saveUnique(is,filename,relevanceId);
        String cellContent = getFromExcel(allTables,"建议（必需）",2,4);
        if(cellContent.endsWith("元")){
            String amoun = cellContent.substring(0,cellContent.indexOf("元"));
            try {
                applicationMapper.updateByPrimaryKeySelective(new JnApplication().setId(appId).setApplyAmount(new BigDecimal(amoun)));
            }catch (NumberFormatException e){
                throw new TableReaderException("内容" + cellContent + "在读取数字时发生错误");
            }
        }else {
            throw new TableReaderException("内容" + cellContent +"没有以元结尾");
        }

    }


    public String getFromExcel(List<AllTable> allTables,String sheetName ,int row , int column) throws TableReaderException{
        AllTable ta = allTables.stream().filter(allTable -> allTable.getName().equals(sheetName)).findFirst().orElse(null);
        if(ta == null) { throw new TableReaderException("错误的excel 没有找到-->" + sheetName);}
        AllRow ar = ta.getAllRows().stream().filter(allRow -> allRow.getRowNum() == row).findFirst().orElse(null);
        if(ar == null) { throw new TableReaderException("错误的excel 没有找到-->" + sheetName + "row-->" + row);}
        AllCell allCell = ar.getAllCells().stream().filter(c -> c.getStartColumn()== column).findFirst().orElse(null);
        if(allCell == null) { throw new TableReaderException("错误的excel 没有找到-->" + sheetName +"-->" + row + "," + column);}
        return allCell.getContent();
    }


    private void saveReplace(AllCellValue cellValue, String relavanceId) {
        AllCellValue dbCellValue = cellValueMapper.selectOne(new AllCellValue()
                .setRelevanceId(relavanceId).setTableId(cellValue.getTableId()).setCellId(cellValue.getCellId()));
        if (dbCellValue == null) {
            cellValueMapper.insertSelective(cellValue);
        } else {
            cellValue.setId(dbCellValue.getId());
            cellValueMapper.updateByPrimaryKeySelective(cellValue);
        }
    }

    private void insert(List<AllTable> allTables, String filename, String relevanceId) {
        AllExcel excel = new AllExcel();
        excel.setName(filename).setCreater(ManagerKit.getUserId()).setCreateTime(new Date()).setRelevanceId(relevanceId);
        excelMapper.insertSelective(excel);
        allTables.forEach(allTable -> insert(allTable, excel));
    }

    private void insert(AllTable allTable, AllExcel excel) {
        allTable.setExcelId(excel.getId());
        allTableMapper.insertSelective(allTable);
        allTable.getAllRows().forEach(allRow -> insert(allRow, allTable));
    }

    private void insert(AllRow allRow, AllTable allTable) {
        allRow.setTableId(allTable.getId());
        rowMapper.insertSelective(allRow);
        allRow.getAllCells().forEach(allCell -> insert(allCell, allRow));
    }

    private void insert(AllCell allCell, AllRow allRow) {
        allCell.setRowId(allRow.getId());
        cellMapper.insertSelective(allCell);
    }

    private AllCell parse(XSSFCell cell, List<CellRangeAddress> ranges, int r, int c) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
        AllCell allCell;

        int matchResult = match(r, c, ranges);
        if (matchResult >= 0) {
            CellRangeAddress rangeAddress = ranges.get(matchResult);
            allCell = new AllCell(r, c, rangeAddress.getLastRow(), rangeAddress.getLastColumn());
        } else if (matchResult == NO_MARGEN) {
            allCell = new AllCell(r, c);
        } else {
            return null;
        }
         cellValue(cell,allCell);
        if (allCell.getContent() != null) {
            allCell.setEditable((short) 1);
        }
        allCell.setStyle(cellStyle(cell));
        return allCell;

    }


    void cellValue(XSSFCell cell,AllCell allCell) {
        String value;
        switch (cell.getCellTypeEnum()) {
            case STRING:
                allCell.setContent(cell.getStringCellValue());
                break;
            case NUMERIC:
                allCell.setContent(cell.getNumericCellValue() + "");
                break;
            case FORMULA:
                // 导入时如果为公式生成的数据则无值
                allCell.setContent(cell.getNumericCellValue() + "");
                allCell.setFormula(cell.getCellFormula());
                break;
            case BOOLEAN:
                allCell.setContent(cell.getBooleanCellValue() ? "Y" : "N");
                break;
            default:
                allCell.setContent("");
        }

    }


    private String cellStyle(XSSFCell cell) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
        XSSFCellStyle cellStyle = cell.getCellStyle();
        if (cellStyle == null) return null;
        StringBuffer sb = new StringBuffer();
        XSSFColor color = cellStyle.getFillForegroundXSSFColor();
        if (color != null) {
            sb.append("background:" + colorStr(color)).append(";");
        }
        XSSFFont font = cellStyle.getFont();
        if (font == null) return sb.toString();
        XSSFColor fontColor = font.getXSSFColor();
        if (fontColor != null) {
            sb.append("color:" + colorStr(fontColor));
        }
        if (font.getBold()) {
            sb.append(";").append("font-weight:bold");
        }
        return sb.toString();
    }


    public String colorStr(XSSFColor color) {
        return "#" + color.getARGBHex().substring(2);
    }


    public static final int IN_MARGEN = -2,
            NO_MARGEN = -1;

    private int match(int r, int c, List<CellRangeAddress> rangeAddresses) {
        for (int i = 0; i < rangeAddresses.size(); i++) {
            CellRangeAddress range = rangeAddresses.get(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (r == firstRow && c == firstColumn) {
                return i;
            }
            if (r >= firstRow && r <= lastRow && c >= firstColumn && c <= lastColumn) {
                return IN_MARGEN;
            }
        }
        return NO_MARGEN;
    }


    private void matchValue(AllCell cell, List<AllCellValue> allCellValues) {
        AllCellValue cellValue = allCellValues.stream().filter(cv -> cell.getId().equals(cv.getCellId())).findFirst().orElse(null);
        if (cellValue != null) {
            cell.setContent(cellValue.getContent());
        }
    }

    private void insertWithTable(AllTable allTable, AllRow r) {
        rowMapper.insertSelective(r.setTableId(allTable.getId()));
        allTable.addRow(r);

    }

    private void createInsert(List<AllRow> allRows, int row, String tableId) {
        boolean anyMatch = allRows.stream().anyMatch(allRow -> allRow.getRowNum() == row);
        if (!anyMatch) {
            allRows.add(new AllRow().setRowNum(row).setTableId(tableId));
        }
    }
}
